|
Excel Macro of the Month
This month’s Excel macro was suggested by reader Ian Johnson – and it's a great idea. Ian uses a macro similar to this to save a workbook using the text in a cell as the file name.
The macro uses a file path which is already hardcoded into it – this needs to be edited before you run the macro the first time. It then assembles a full file name using the built in path and the contents of the currently selected cell. The macro checks to see if the file already exists and, if it does, then you’ll see a message box with a prompt telling you that this is the case and the macro ends – it doesn't overwrite the existing file. Otherwise an attempt will be made to save the file with the given file name. Having done this, the macro then checks again to see if the file name exists and reports that the file has been saved if that is the case. If the file has not been saved then a message displays telling you that you need to select a cell with something in it that can be used as a valid file name. You won’t see a prompt telling you that the file has been saved unless the file has actually been saved and, of course, you can always confirm this by checking the file name in the Excel title bar.
The On Error Resume Next statement is used to ensure that the macro continues to run without stopping if it encounters any minor problems in execution.
The macro is in a file called SaveWithCustomName.xls on the cover disc and this file contains details about the macro and how to edit it.
|
